package dexter.aware.db;

import java.sql.*;
import java.util.Collection;
import java.util.Hashtable;
import java.util.Vector;

public class DatabaseManager {
    
    //private static Connection conn;
    
    public static String STATE_TABLE = "uoc.STATE";
    public static String HIST_TABLE = "uoc.HIST";
    
    private static ThreadLocal<Connection> transactionConn = new ThreadLocal();
	private static String dbUrl;
	private static String dbUser;
	private static String dbPwd;
    
    private static Connection init(String driver, String dbUrl, String dbUser, String dbPwd) {
		Connection conn = null;
		try {
			Class.forName (driver);
			System.out.println ("Connection to " + dbUrl);
			//DatabaseManager.conn = DriverManager.getConnection (dbUrl, dbUser, dbPassword);;
			DatabaseManager.dbUrl = dbUrl;
			DatabaseManager.dbUser = dbUser;
			DatabaseManager.dbPwd = dbPwd;
		} catch (ClassNotFoundException e) {
			System.err.println ("Driver not found : "+driver);
			e.printStackTrace();
		//} catch (SQLException e) {
		//	System.err.println ("Error: not connected with : "+dbUrl);
		//	e.printStackTrace();
		}
		return conn;
	}
    
    public static synchronized void beginTransaction() throws Exception {
        if ( transactionConn.get() != null ) {
            throw new Exception("This thread is already in a transaction");
        }
        Connection conn = getConnection();
        conn.setAutoCommit(false);
        transactionConn.set(conn);
    }
    
    public static void commitTransaction() throws Exception {
        if ( transactionConn.get() == null ) {
            throw new Exception("Can't commit: this thread isn't currently in a " +
                    "transaction");
        }
        transactionConn.get().commit();
        transactionConn.set(null);
    }
    
    public static void rollbackTransaction() throws Exception {
        if ( transactionConn.get() == null ) {
            throw new Exception("Can't rollback: this thread isn't currently in a " +
                    "transaction");
        }
        transactionConn.get().rollback();
        transactionConn.set(null);
    }

    public static Connection getConnection() throws Exception {
        if ( transactionConn.get() != null ) {
            return transactionConn.get();
        } else {
        	Connection conn = DriverManager.getConnection (dbUrl, dbUser, dbPwd);
            return conn;
        }
    }
    
    public static void releaseConnection(Connection conn) throws Exception {
         if ( transactionConn.get() == null ) {
            conn.close();
        }
    }
        
    public static void initDatabase(String driver, String dbUrl, String dbUser, String dbPassword,
            boolean dropTables) 
        throws Exception {
        
    	init(driver, dbUrl, dbUser, dbPassword);

        if ( dropTables ) {
            dropTables();
            createTables();
        }
     
        //if ( ! tableExists("STATE") ) {
        //    createTables();
        //}
    }
    
    private static boolean tableExists(String tablename) throws Exception {
        Connection conn = getConnection();
        ResultSet rs;
        boolean exists;
        
        try {
            DatabaseMetaData md = conn.getMetaData();
        
            rs = md.getTables(null, "uoc", tablename, null);
            exists = rs.next();
        } finally {
            releaseConnection(conn);
        }
        
        return exists;
    } 
    
    private static void createTables() throws Exception {
        System.out.println("Creating tables");
        
        executeUpdate(
                "CREATE TABLE " + STATE_TABLE + "(" +
                "hostname VARCHAR(256) PRIMARY KEY, " +
                "cpu INTEGER, " +
                "mem INTEGER, " +
                "disk INTEGER, " +
                "time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
        
        executeUpdate(
                "CREATE TABLE " + HIST_TABLE + "(" +
                "sequence_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
                "hostname VARCHAR(256), " +
                "cpu INTEGER, " +
                "mem INTEGER, " +
                "disk INTEGER, " +
                "time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
    }
    
    public static void dropTables() {
        try {
            executeUpdate("DROP TABLE " + STATE_TABLE);
            executeUpdate("DROP TABLE " + HIST_TABLE);
        } catch ( Exception e ) {
            e.printStackTrace();
        }
    }
    
    private static boolean tableDoesntExist(String sqlState) {
        return sqlState.equals("42X05") ||
               sqlState.equals("42Y55");
    }

    public static void clearTables() throws Exception {
        Connection conn = getConnection();
        
        try {
            executeUpdate("DELETE FROM " + STATE_TABLE);       
            executeUpdate("DELETE FROM " + HIST_TABLE); 
        } finally {
            releaseConnection(conn);
        }
        
    }
    
    private static int executeUpdate(String statement) 
            throws Exception {
        Connection conn = getConnection();
        try {
           PreparedStatement ps = conn.prepareStatement(statement);
           return ps.executeUpdate();
        } finally {
            releaseConnection(conn);
        }
    }
    
    public static void persistState(String hostname, int[] state, long millis) {
		try {
			int cpu = state[0];
			int mem = state[1];
			int disk = state[2];
			java.sql.Timestamp  sqlDate = new java.sql.Timestamp(millis);
			try {
			DatabaseManager.executeUpdate("insert into "+DatabaseManager.STATE_TABLE+" (hostname, cpu, mem, disk, time) VALUES ('"+hostname+"', "+cpu+", "+mem+","+disk+", '"+sqlDate+"')");
			} catch(Exception e) {
				DatabaseManager.executeUpdate("update "+DatabaseManager.STATE_TABLE+" SET cpu = "+cpu+", mem = "+mem+", disk = "+disk+", time = '"+sqlDate+"' WHERE hostname LIKE '"+hostname+"'");
			}
			DatabaseManager.executeUpdate("insert into "+DatabaseManager.HIST_TABLE+" (hostname, cpu, mem, disk, time) VALUES ('"+hostname+"', "+cpu+", "+mem+","+disk+", '"+sqlDate+"')");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
    

    private static ResultSet executeQueryNoParams(String statement) throws Exception {
    	Connection conn = getConnection();    	
    	PreparedStatement ps = conn.prepareStatement(statement);
       return ps.executeQuery();
    }
	
	public static Hashtable<String, int[]> findAllStates() {
		
		Hashtable<String,int[]> hosts = new Hashtable<String,int[]>();
		
		try {
			ResultSet rs = executeQueryNoParams("SELECT * FROM "
					+ STATE_TABLE + " ORDER BY time DESC");
			while (rs.next()) {
				String hostname = rs.getString(1);
				int cpu = rs.getInt(2);
				int mem = rs.getInt(3);
				int disk = rs.getInt(4);
				java.sql.Timestamp time = rs.getTimestamp(5);
				hosts.put(hostname, new int[]{cpu,mem,disk});
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hosts;
	}
	

	public static Hashtable<String, int[]> findAllStatesByTime(Timestamp min) {
		Hashtable<String,int[]> hosts = new Hashtable<String,int[]>();
		
		System.out.println("AWARE : SELECT HOST.time > "+min);
		try {
			ResultSet rs = executeQueryNoParams("SELECT * FROM "
					+ STATE_TABLE
					+ " WHERE time > '"+min+"'"
					+ " ORDER BY time DESC");
			while (rs.next()) {
				String hostname = rs.getString(1);
				int cpu = rs.getInt(2);
				int mem = rs.getInt(3);
				int disk = rs.getInt(4);
				java.sql.Timestamp time = rs.getTimestamp(5);
				hosts.put(hostname, new int[]{cpu,mem,disk});
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hosts;

	}

}
